<?php

class Db_Joins_ReportsJoins
{
	var $date_start;
	var $date_end;
	var $group_id;
	var $factory_id;
	var $province_id;
	var $city_id;
	var $emp_auth_row;
	var $keyword;
	
	var $sort_type;
	var $page_id;
	var $page_size = 30;
	var $skip_pagination;
	var $product_id;
	var $version_id;
	var $emp_id;
	
	var $start_date_start;
	var $start_date_end;
	var $expired_date_start;
	var $expired_date_end;
	
	var $call_in_num;
	var $call_phone;
	var $call_in_out;
	var $is_pick;
	var $user_name;
	var $company_id;
	
	
	function MakeStatForReport($data)
	{
		$result = array(
			"fabu" => NULL,
			"contract_value" => NULL,
			"receivable_value" => NULL,
			"contract_ct" => NULL,
			"service_ct" => NULL,
			"col_title" => NULL,
			"details" => array()
		);
		
		if(!empty($data))
		{
			$start_year = $start_month = $end_year = $end_month = 0;
			$contract_id_arr = array();
			
			foreach($data as $d)
			{
				$contract_id_arr[] = $d["contract_id"];
			}
			
			if(!empty($contract_id_arr))
			{
				$dealers_db = Zend_Registry::get('dealers_db');
				
				$select = $dealers_db->select();
				$select->from("b_contract", array(
						"max(contract_expired_date) as max_contract_expired_date",
						"min(contract_start_date) as min_contract_start_date"
					));
				$select->where("contract_id IN (?)", $contract_id_arr);
				$row = $dealers_db->fetchRow($select);
				
				if($row)
				{
					$start_year = substr($row['min_contract_start_date'],0,4);
					$start_month = substr($row['min_contract_start_date'],5,2);
					$end_year = substr($row['max_contract_expired_date'],0,4);
					$end_month = substr($row['max_contract_expired_date'],5,2);
				}
			}
			
			if($start_year && $start_month && $end_year && $end_month)
			{
				$start_signer = $start_year.$start_month;
				$stop_signer = $end_year.$end_month;
				
				while($start_signer <= $stop_signer)
				{
					$result['col_title'][$start_signer] = $start_year."年".$start_month."月";
					
					$ori_start_year = $start_year;
					$ori_start_month = $start_month;
					
					$start_year = date("Y", mktime(1,0,0,$ori_start_month+1,1,$ori_start_year));
					$start_month = date("m", mktime(1,0,0,$ori_start_month+1,1,$ori_start_year));
					
					$start_signer = $start_year.$start_month;
				}
			}
		}
//		$ext = new Sys_Extensions();
//		$ext->FormatArray($result);die;
		return $result;
	}
	
	function HxbStatus()
	{
		$result = array(
			'ct' => 0,
			'total_pages' => 0,
			'current_page_info' => array()
		);
		
		//my dealer ids
		$arr = array();
		
		if($this->group_id)
		{
			$arr['group_id'] = $this->group_id;
		}
		
		if($this->province_id)
		{
			$arr['province_id'] = $this->province_id;
		}
		
		if($this->city_id)
		{
			$arr['city_id'] = $this->city_id;
		}
		
		if($this->factory_id)
		{
			$arr['factory_id'] = $this->factory_id;
		}
		
		$mod_user = new Db_Tables2_User();
		$my_dealer_ids = $mod_user->GetFilteredDealerIds($arr, $this->emp_auth_row);
		
		$dealers_db = Zend_Registry::get("dealers_db");
		
		if($this->keyword)
		{
			$keyword_dealer_id = array();
			
			$select = $dealers_db->select();
			$select->from("t_dealer", array("id"));
			$select->where("name like '%".$this->keyword."%' or companyName like '%".$this->keyword."%' or address like '%".$this->keyword."%' or tel like '%".$this->keyword."%' or tel400 like '%".$this->keyword."%'");
			$rows = $dealers_db->fetchAll($select);
			
			if(!empty($rows))
			{
				foreach($rows as $row)
				{
					$keyword_dealer_id[] = $row['id'];
				}
			}
			
			$my_dealer_ids = array_intersect($keyword_dealer_id, $my_dealer_ids);
			$my_dealer_ids = array_unique($my_dealer_ids);
		}
		
		if(!empty($my_dealer_ids))
		{
			$hxb_db = Zend_Registry::get("hxb_db");
			
			//count data
			$data = $hxb_db->select();
			$data->from("t_hxb_oc_log", array("dealerId", "count(*) as ct"));

			if($this->date_start)
			{
				$data->where("date >= ?", $this->date_start);
			}

			if($this->date_end)
			{
				$data->where("date <= ?", $this->date_end);
			}

			$data->where("dealerId IN (?)", $my_dealer_ids);
			
			$data->group("dealerId");
			
			$ct = $hxb_db->fetchAll($data);
			
			//dump all in condition
			$data = $hxb_db->select();
			$data->from("t_hxb_oc_log", array("dealerId", "sum(dayLeadsCount) as day_leads_ct", "sum(dayActiveCount) as day_active_ct"));

			if($this->date_start)
			{
				$data->where("date >= ?", $this->date_start);
			}

			if($this->date_end)
			{
				$data->where("date <= ?", $this->date_end);
			}

			$data->where("dealerId IN (?)", $my_dealer_ids);
			
			if(2 == $this->sort_type)
			{
				$data->order("day_active_ct DESC"); //按活跃度
			}else{
				$data->order("day_leads_ct DESC"); //按线索量
			}

			if($this->page_id && !$this->skip_pagination)
			{
				$data->limit($this->page_size, $this->page_size * ($this->page_id - 1));
			}
			
			$data->group("dealerId");
			
			$origine = $hxb_db->fetchAll($data);
//			$ext = new Sys_Extensions();
//			$ext->FormatArray($origine);die;
			$current_page_info = array();
			
			if(!empty($origine))
			{
				$ids_dealer = array();

				$current_page_info = array(
					"day_leads_ct" => array(),
					"day_active_ct" => array()
				);

				foreach($origine as $row)
				{
					$ids_dealer[] = $row['dealerId'];

					$current_page_info['day_leads_ct_for_sort'][$row['dealerId']] = $row['day_leads_ct'];
					$current_page_info['day_active_ct'][$row['dealerId']] = $row['day_active_ct'];
				}
				
				$current_page_info['day_leads_ct'] = array();
				
				if(!empty($ids_dealer)) //get dealer info
				{
					$select = $dealers_db->select();
					$select->from("t_dealer as d", array("id", "_dealerId", "companyName", "provinceName", "cityName", "date(authTime) as authTime"));
					$select->joinLeft("t_dealer_group as g", "g.id=d.groupId", "name as groupName");
					$select->joinLeft("b_client as c", "c.is_dealer=d.id", array());
					$select->joinLeft("hxb_db.auth_emp as a", "a.id=c.emp_id", array("realName"));
					$select->where("d.id IN (?)", $ids_dealer);
					$rows = $dealers_db->fetchAll($select);

					if($rows)
					{
						foreach($rows as $row)
						{
							$current_page_info['_dealerId'][$row['id']] = $row['_dealerId'];
							$current_page_info['companyName'][$row['id']] = $row['companyName'];
							$current_page_info['provinceName'][$row['id']] = $row['provinceName'];
							$current_page_info['cityName'][$row['id']] = $row['cityName'];
							$current_page_info['groupName'][$row['id']] = $row['groupName'];
							$current_page_info['authTime'][$row['id']] = $row['authTime'];
							$current_page_info['realName'][$row['id']] = $row['realName'];
						}
					}
					
					$select = $dealers_db->select();
					$select->from("hxb_db.t_customer_leads", array("distinct(phoneNum)", "dealerId"));
					
					if($this->date_start)
					{
						$select->where("createDate >= ?", $this->date_start);
					}

					if($this->date_end)
					{
						$select->where("createDate <= ?", $this->date_end);
					}
					
					$select->where("dealerId IN (?)", $ids_dealer);
					$rows = $dealers_db->fetchAll($select);
					
					$leads_ct_result = array();

					if($rows)
					{
						foreach($rows as $row)
						{
							if(!isset($current_page_info['day_leads_ct'][$row['dealerId']]))
							{
								$current_page_info['day_leads_ct'][$row['dealerId']] = 1;
							}else{
								$current_page_info['day_leads_ct'][$row['dealerId']] += 1;
							}
						}
					}
				}
				
				//make factory string
				$current_page_info['factoryName'] = array();
                
                $mod_params = new Db_Tables2_BDictParams();
                $sys_id = $mod_params->GetSysId();
				
				if(!empty($ids_dealer))
				{
					$select = $dealers_db->select();
					$select->from("dealers_db".$sys_id.".t_dealer_factory as d", array("dealerId"));
					$select->joinLeft("cars_db".$sys_id.".t_factory as f", "f.id=d.factoryId", array("name"));
					$select->where("d.dealerId IN (?)", $ids_dealer);
					$rows = $dealers_db->fetchAll($select);
					
					$fac = array();
					
					if(!empty($rows))
					{
						foreach($rows as $row)
						{
							if(!isset($fac[$row['dealerId']]))
							{
								$fac[$row['dealerId']] = array();
							}
							
							$fac[$row['dealerId']][] = $row['name'];
						}
					}
					
					if(!empty($fac))
					{
						foreach($fac as $dealer_id => $f)
						{
							$current_page_info['factoryName'][$dealer_id] = implode(", ", $f);
						}
					}
				}
			}

			$result = array(
				'ct' => count($ct),
				'total_pages' => ceil(count($ct) / $this->page_size),
				'current_page_info' => $current_page_info
			);
		}
		
		return $result;
	}
	
	function ContractExport1() //合同管理员使用，仅导出EXCEL
	{
		$result = array(
			'current_page_info' => array()
		);
		
		//count total for pagination
		$dealers_db = Zend_Registry::get('dealers_db');
		$mod_client = new Db_Tables2_BClient();
		$mod_sku_service = new Db_Tables2_BContractSkuService();
		$mod_sku = new Db_Tables2_BSku();
		
		//dump all in condition
		$data = $dealers_db->select();
		$data->from("b_contract_sku as s", array("cs_id", "product_name", "sku_id", "quantity", "dealer_id"));
		$data->joinLeft("b_contract as c", "c.contract_id=s.contract_id", array(
			"contract_id",
			"contract_sid",
			"contract_type_id",
			"province_name",
			"city_name",
			"pay_type",
			"start_date",
			"expired_date",
			"sign_date",
			"sales_emp_name",
			"client_id",
			"client_name",
			"signer_company_name",
			"owner_name",
			"contract_value",
			"receivable_value",
			"received_value",
            "tips",
            "status"));
		$data->joinLeft("b_client as t", "t.client_id=c.client_id", array());
		$data->joinLeft("b_client_type as y", "y.client_type_id=t.client_type_id", array("name as client_type"));
		if($this->start_date_start)
		{
			$data->where("c.start_date >= ?", $this->start_date_start);
		}
		
		if($this->start_date_end)
		{
			$data->where("c.start_date <= ?", $this->start_date_end);
		}
		
		if($this->expired_date_start)
		{
			$data->where("c.expired_date >= ?", $this->expired_date_start);
		}
		
		if($this->expired_date_end)
		{
			$data->where("c.expired_date <= ?", $this->expired_date_end);
		}
		
		if($this->sales_emp_id)
		{
			$data->where("c.sales_emp_id = ?", $this->sales_emp_id);
		}elseif("LEVEL-2" == $_SESSION['identity']['level']) //销售主管只可以看属于自己的员工合同
		{
			$mod_user = new Db_Tables2_User();
			$my_staff_ids = $mod_user->GetMyStaffIds($_SESSION['identity']['userid']);
			
			if(!empty($my_staff_ids))
			{
				$data->where("c.sales_emp_id IN (?)", $my_staff_ids);
			}
		}
		
		if($this->owner_id)
		{
			$data->where("c.owner_id = ?", $this->owner_id);
		}
		
		if($this->contract_type)
		{
			if(1 == $this->contract_type)
			{
				$data->where("c.parent_contract_id is NULL or c.parent_contract_id=''");
			}elseif(2 == $this->contract_type)
			{
				$data->where("c.parent_contract_id is not NULL and c.parent_contract_id!=''");
			}
		}
		
		if($this->renew_type)
		{
			$date_30 = date("Y-m-d", mktime(1,1,1, date("m"), date("d")+30, date("Y")));
			
			if(1 == $this->renew_type)
			{
				$data->where("c.expired_date <= ?", $date_30);
			}elseif(2 == $this->renew_type)
			{
				$data->where("c.expired_date <= ?", date("Y-m-d"));
			}elseif(3 == $this->renew_type)
			{
				$data->where("c.expired_date > ?", $date_30);
			}
		}
		
		if($this->product_id)
		{
			$data->where("s.product_id = ?", $this->product_id);
		}
		
		if($this->province_id)
		{
			$data->where("c.province_id = ?", $this->province_id);
		}
		
		if($this->city_id)
		{
			$data->where("c.city_id = ?", $this->city_id);
		}
		
		if(is_numeric($this->status))
		{
			$data->where("c.status = ?", $this->status);
		}else{
			$data->where("c.status != ?", 7); //默认不显示作废
		}
		
		if($this->keyword)
		{
			$data->where("c.contract_sid like '%".$this->keyword."%' or c.parent_contract_sid like '%".$this->keyword."%' or c.client_name like '%".$this->keyword."%' or c.signer_company_name like '%".$this->keyword."%'");
		}
		
		if($this->skip_pagination)
		{
			$data->where("c.status != ?", 7);
			$data->where("c.contract_sid is not NULL or c.contract_sid!=''");
		}

		if($this->page_id && !$this->skip_pagination)
		{
			$data->limit($this->page_size, $this->page_size * ($this->page_id - 1));
		}

        $data->group("c.contract_sid");
        $data->order(array("s.cs_id DESC"));

        $origine = $dealers_db->fetchAll($data);
//		$ext = new Sys_Extensions();
//		$ext->FormatArray($origine);die;
		$current_page_info = array();
		
		if(!empty($origine))
		{
			$contract_id_arr = array();
			$cs_id_arr = array();
			
			foreach($origine as $row)
			{
				$contract_id_arr[] = $row['contract_id'];
				$cs_id_arr[] = $row['cs_id'];
			}
			
			$contract_id_arr = array_unique($contract_id_arr);
			
			if(!empty($contract_id_arr))
			{
				$mod_logs_contract = new Db_Tables2_BLogsContract();
				$payment_time_arr = $mod_logs_contract->GetPaymentTime($contract_id_arr);
			}else{
				$payment_time_arr = array();
			}
			
			$service_info = $mod_sku_service->GetServiceStr($cs_id_arr, 1);
			
			//start generation
			$current_page_info = array(
				"contract_id" => array(),
				"contract_sid" => array(),
				"contract_type_id" => array(),
				"province_name" => array(),
				"city_name" => array(),
                "pay_type" => array(),
                "product_name" => array(),
				"brands" => array(),
				"factories" => array(),
				"start_date" => array(),
				"expired_date" => array(),
				"sign_date" => array(),
				"sales_emp_name" => array(),
				"client_name" => array(),
				"signer_company_name" => array(),
				"owner_name" => array(),
				"dealer_id" => array(),
				"contract_value" => array(),
				"receivable_value" => array(),
				"received_value" => array(),
                "tips" => array(),
                "ctime" => array(),
				"channel_info" => array(),
				"service_info" => array(),
				"client_type" => array(),
				"payment_time" => array(),
				"status" => array()
			);
			
			if($this->product_id)
			{
				switch($this->product_id)
				{
					case 1: //代运营
						$current_page_info['service_count'] = array();
						$current_page_info['services'] = array();
						break;
					case 2: //汇销宝
						$current_page_info['version'] = array();
						$current_page_info['quantity'] = array();
						break;
					case 3: //DCC
						$current_page_info['version'] = array();
						$current_page_info['quantity'] = array();
						break;
				}
			}
			
			foreach($origine as $row)
			{
				$client_info = $mod_client->FetchClientDetail($row['client_id'], 1);
				
				$current_page_info['contract_id'][$row['cs_id']] = $row['contract_id'];
				$current_page_info['contract_sid'][$row['cs_id']] = $row['contract_sid'];
				$current_page_info['contract_type_id'][$row['cs_id']] = $row['contract_type_id'];
				$current_page_info['product_name'][$row['cs_id']] = $row['product_name'];
				$current_page_info['province_name'][$row['cs_id']] = $row['province_name'];
				$current_page_info['city_name'][$row['cs_id']] = $row['city_name'];
                $current_page_info['pay_type'][$row['cs_id']] = $row['pay_type'];
                $current_page_info['brands'][$row['cs_id']] = $client_info['additional_info']['brand_info']['str'];
				$current_page_info['factories'][$row['cs_id']] = $client_info['additional_info']['factory_info']['str'];
				$current_page_info['start_date'][$row['cs_id']] = $row['start_date'];
				$current_page_info['expired_date'][$row['cs_id']] = $row['expired_date'];
				$current_page_info['sign_date'][$row['cs_id']] = $row['sign_date'];
				$current_page_info['sales_emp_name'][$row['cs_id']] = $row['sales_emp_name'];
				$current_page_info['client_name'][$row['cs_id']] = $row['client_name'];
				$current_page_info['signer_company_name'][$row['cs_id']] = $row['signer_company_name'];
				$current_page_info['owner_name'][$row['cs_id']] = $row['owner_name'];
				$current_page_info['dealer_id'][$row['cs_id']] = $row['dealer_id'];
				$current_page_info['contract_value'][$row['cs_id']] = $row['contract_value'];
				$current_page_info['receivable_value'][$row['cs_id']] = $row['receivable_value'];
				$current_page_info['received_value'][$row['cs_id']] = $row['received_value'];
                $current_page_info['tips'][$row['cs_id']] = $row['tips'];
                $current_page_info['client_type'][$row['cs_id']] = $row['client_type'];
                $current_page_info['status'][$row['cs_id']] = $row['status'];
				$current_page_info['payment_time'][$row['cs_id']] = $payment_time_arr[$row['contract_id']]?$payment_time_arr[$row['contract_id']]:NULL;
				
				if($this->product_id)
				{
					switch($this->product_id)
					{
						case 1: //代运营
							if(!empty($service_info['arr'][$row['cs_id']]))
							{
								$current_page_info['service_count'][$row['cs_id']] = count($service_info['arr'][$row['cs_id']]);
								$current_page_info['services'][$row['cs_id']] = $service_info['arr'][$row['cs_id']];
							}else{
								$current_page_info['service_count'][$row['cs_id']] = 0;
								$current_page_info['services'][$row['cs_id']] = array();
							}
							break;
						case 2: //汇销宝
							$current_page_info['version'][$row['cs_id']] = "";
							$current_page_info['quantity'][$row['cs_id']] = $row['quantity'];
							
							$path = $mod_sku->GetSkuPath($row['sku_id']);
							if($path['path_name'])
							{
								$path_arr = explode(">", $path['path_name']);
								
								if(!empty($path_arr))
								{
									foreach($path_arr as $pkey => $pval)
									{
										$path_arr[$pkey] = trim($pval);
									}
									
									foreach($path_arr as $pval)
									{
										if(is_numeric(strpos($pval, "会员版")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = "会员版";
										}elseif(is_numeric(strpos($pval, "Power")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = "线索包—P版";
										}elseif(is_numeric(strpos($pval, "Turbo")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = "线索包—T版";
										}
									}
								}
							}
							break;
						case 3: //DCC
							$current_page_info['version'][$row['cs_id']] = "";
							$current_page_info['quantity'][$row['cs_id']] = $row['quantity'];
							
							$path = $mod_sku->GetSkuPath($row['sku_id']);
							if($path['path_name'])
							{
								$path_arr = explode(">", $path['path_name']);
								
								if(!empty($path_arr))
								{
									foreach($path_arr as $pkey => $pval)
									{
										$path_arr[$pkey] = trim($pval);
									}
									
									foreach($path_arr as $pval)
									{
										if(is_numeric(strpos($pval, "省心")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = $pval;
										}elseif(is_numeric(strpos($pval, "星夜")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = $pval;
										}elseif(is_numeric(strpos($pval, "活动")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = $pval;
										}elseif(is_numeric(strpos($pval, "清洗")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = $pval;
										}elseif(is_numeric(strpos($pval, "唤醒")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = $pval;
										}
									}
								}
							}
							break;
					}
				}
			}
		}
		
		$result = array(
			'current_page_info' => $current_page_info
		);
		
		return $result;
	}
	
	function ContractExport2() //合同管理员使用，仅导出EXCEL
	{
		$result = array(
			'current_page_info' => array()
		);
		
		//count total for pagination
		$dealers_db = Zend_Registry::get('dealers_db');
		$mod_client = new Db_Tables2_BClient();
		$mod_sku_service = new Db_Tables2_BContractSkuService();
		$mod_sku = new Db_Tables2_BSku();
		
		//dump all in condition
		$data = $dealers_db->select();
		$data->from("b_contract_sku as s", array("cs_id", "sku_id", "product_name", "actual_pay", "start_date", "expired_date"));
		$data->joinLeft("b_contract as c", "c.contract_id=s.contract_id", array("contract_sid", "sign_date", "signer_company_name", "client_name", "sales_emp_name", "owner_name", "province_name", "city_name", "sign_type", "contract_type_id"));
		$data->joinLeft("b_client as l", "l.client_id=c.client_id", array());
		$data->joinLeft("t_dealer as d", "d.id=l.is_dealer", array("type as dealer_type", "id as dealer_id", "companyName as dealer_name"));
		$data->joinLeft("b_dealer_extrainfo as e", "e._dealerId=d._dealerId", array("channel_txt", "brand_txt"));
		
		if($this->start_date_start)
		{
			$data->where("c.start_date >= ?", $this->start_date_start);
		}
		
		if($this->start_date_end)
		{
			$data->where("c.start_date <= ?", $this->start_date_end);
		}
		
		if($this->expired_date_start)
		{
			$data->where("c.expired_date >= ?", $this->expired_date_start);
		}
		
		if($this->expired_date_end)
		{
			$data->where("c.expired_date <= ?", $this->expired_date_end);
		}
		
		if($this->sales_emp_id)
		{
			$data->where("c.sales_emp_id = ?", $this->sales_emp_id);
		}elseif("LEVEL-2" == $_SESSION['identity']['level']) //销售主管只可以看属于自己的员工合同
		{
			$mod_user = new Db_Tables2_User();
			$my_staff_ids = $mod_user->GetMyStaffIds($_SESSION['identity']['userid']);
			
			if(!empty($my_staff_ids))
			{
				$data->where("c.sales_emp_id IN (?)", $my_staff_ids);
			}
		}
		
		if($this->owner_id)
		{
			$data->where("c.owner_id = ?", $this->owner_id);
		}
		
		if($this->contract_type)
		{
			if(1 == $this->contract_type)
			{
				$data->where("c.parent_contract_id is NULL or c.parent_contract_id=''");
			}elseif(2 == $this->contract_type)
			{
				$data->where("c.parent_contract_id is not NULL and c.parent_contract_id!=''");
			}
		}
		
		if($this->renew_type)
		{
			$date_30 = date("Y-m-d", mktime(1,1,1, date("m"), date("d")+30, date("Y")));
			
			if(1 == $this->renew_type)
			{
				$data->where("c.expired_date <= ?", $date_30);
			}elseif(2 == $this->renew_type)
			{
				$data->where("c.expired_date <= ?", date("Y-m-d"));
			}elseif(3 == $this->renew_type)
			{
				$data->where("c.expired_date > ?", $date_30);
			}
		}
		
		if($this->product_id)
		{
			$data->where("s.product_id = ?", $this->product_id);
		}
		
		if($this->province_id)
		{
			$data->where("c.province_id = ?", $this->province_id);
		}
		
		if($this->city_id)
		{
			$data->where("c.city_id = ?", $this->city_id);
		}
		
		if(is_numeric($this->status))
		{
			$data->where("c.status = ?", $this->status);
		}else{
			$data->where("c.status != ?", 7); //默认不显示作废
		}
		
		if($this->keyword)
		{
			$data->where("c.contract_sid like '%".$this->keyword."%' or c.parent_contract_sid like '%".$this->keyword."%' or c.client_name like '%".$this->keyword."%' or c.signer_company_name like '%".$this->keyword."%'");
		}
		
		if($this->skip_pagination)
		{
			$data->where("c.status != ?", 7);
			$data->where("c.contract_sid is not NULL or c.contract_sid!=''");
		}

		if($this->page_id && !$this->skip_pagination)
		{
			$data->limit($this->page_size, $this->page_size * ($this->page_id - 1));
		}
		
		$data->order(array("s.cs_id DESC"));
        $data->group("c.contract_sid");

        $origine = $dealers_db->fetchAll($data);
//		$ext = new Sys_Extensions();
//		$ext->FormatArray($origine);die;
		$current_page_info = array();
		
		if(!empty($origine))
		{
			$cs_id_arr = array();
			
			foreach($origine as $row)
			{
				$contract_id_arr[] = $row['contract_id'];
				$cs_id_arr[] = $row['cs_id'];
			}
			
			$service_info = $mod_sku_service->GetServiceStr($cs_id_arr, 1);
			
			//start generation
			$current_page_info = array(
				"product_name" => array(),
				"actual_pay" => array(),
				"start_date" => array(),
				"expired_date" => array(),
				"contract_sid" => array(),
				"sign_date" => array(),
				"signer_company_name" => array(),
				"client_name" => array(),
				"sales_emp_name" => array(),
				"owner_name" => array(),
				"province_name" => array(),
				"city_name" => array(),
				"dealer_type" => array(),
				"dealer_id" => array(),
				"dealer_name" => array(),
				"channel_txt" => array(),
				"brand_txt" => array(),
				"sign_type" => array(),
				"contract_type_id" => array()
			);
			
			if($this->product_id)
			{
				switch($this->product_id)
				{
					case 1: //代运营
						$current_page_info['services'] = array();
						break;
					case 2: //汇销宝
						$current_page_info['version'] = array();
						$current_page_info['quantity'] = array();
						break;
					case 3: //DCC
						$current_page_info['version'] = array();
						$current_page_info['quantity'] = array();
						break;
				}
			}
			
			foreach($origine as $row)
			{
				$client_info = $mod_client->FetchClientDetail($row['client_id'], 1);
				
				$current_page_info['product_name'][$row['cs_id']] = $row['product_name'];
				$current_page_info['actual_pay'][$row['cs_id']] = $row['actual_pay'];
				$current_page_info['start_date'][$row['cs_id']] = $row['start_date'];
				$current_page_info['expired_date'][$row['cs_id']] = $row['expired_date'];
				$current_page_info['contract_sid'][$row['cs_id']] = $row['contract_sid'];
				$current_page_info['sign_date'][$row['cs_id']] = $row['sign_date'];
				$current_page_info['signer_company_name'][$row['cs_id']] = $row['signer_company_name'];
				$current_page_info['client_name'][$row['cs_id']] = $row['client_name'];
				$current_page_info['sales_emp_name'][$row['cs_id']] = $row['sales_emp_name'];
				$current_page_info['owner_name'][$row['cs_id']] = $row['owner_name'];
				$current_page_info['province_name'][$row['cs_id']] = $row['province_name'];
				$current_page_info['city_name'][$row['cs_id']] = $row['city_name'];
				$current_page_info['dealer_type'][$row['cs_id']] = $row['dealer_type'];
				$current_page_info['dealer_id'][$row['cs_id']] = $row['dealer_id'];
				$current_page_info['dealer_name'][$row['cs_id']] = $row['dealer_name'];
				$current_page_info['channel_txt'][$row['cs_id']] = $row['channel_txt'];
				$current_page_info['brand_txt'][$row['cs_id']] = $row['brand_txt'];
				$current_page_info['sign_type'][$row['cs_id']] = $row['sign_type'];
				$current_page_info['contract_type_id'][$row['cs_id']] = $row['contract_type_id'];
				
				if($this->product_id)
				{
					switch($this->product_id)
					{
						case 1: //代运营
							if(!empty($service_info['arr'][$row['cs_id']]))
							{
								$current_page_info['services'][$row['cs_id']] = $service_info['arr'][$row['cs_id']];
							}else{
								$current_page_info['services'][$row['cs_id']] = array();
							}
							break;
						case 2: //汇销宝
							$current_page_info['version'][$row['cs_id']] = "";
							
							$path = $mod_sku->GetSkuPath($row['sku_id']);
							if($path['path_name'])
							{
								$path_arr = explode(">", $path['path_name']);
								
								if(!empty($path_arr))
								{
									foreach($path_arr as $pkey => $pval)
									{
										$path_arr[$pkey] = trim($pval);
									}
									
									foreach($path_arr as $pval)
									{
										if(is_numeric(strpos($pval, "会员版")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = "会员版";
										}elseif(is_numeric(strpos($pval, "Power")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = "线索包";
										}elseif(is_numeric(strpos($pval, "Turbo")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = "线索包";
										}
									}
								}
							}
							break;
						case 3: //DCC
							$current_page_info['version'][$row['cs_id']] = "";
							
							$path = $mod_sku->GetSkuPath($row['sku_id']);
							if($path['path_name'])
							{
								$path_arr = explode(">", $path['path_name']);
								
								if(!empty($path_arr))
								{
									foreach($path_arr as $pkey => $pval)
									{
										$path_arr[$pkey] = trim($pval);
									}
									
									foreach($path_arr as $pval)
									{
										if(is_numeric(strpos($pval, "省心")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = $pval;
										}elseif(is_numeric(strpos($pval, "清洗")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = $pval;
										}elseif(is_numeric(strpos($pval, "唤醒")) && !$current_page_info['version'][$row['cs_id']])
										{
											$current_page_info['version'][$row['cs_id']] = $pval;
										}
									}
								}
							}
							break;
					}
				}
			}
		}
//		$ext = new Sys_Extensions();
//		$ext->FormatArray($current_page_info);die;
		$result = array(
			'current_page_info' => $current_page_info
		);
		
		return $result;
	}
	
	function SalesMain()
	{
		$result = array();
		
		$my_staff_ids = array($_SESSION['identity']['userid']);
		
		$mod_user = new Db_Tables2_User();
		
		if("LEVEL-2" == $_SESSION['identity']['level']){
			if($this->sales_emp_id)
			{
				$my_staff_ids = array($this->sales_emp_id);
			}else{
				$my_staff_ids = $mod_user->GetMyStaffIds($_SESSION['identity']['userid']);
			}
		}
		
		$staff_names = $mod_user->GetStaffNames($my_staff_ids);
		
		foreach($my_staff_ids as $staff_id)
		{
			$result[$staff_id] = array(
				"emp_name" => $staff_names[$staff_id],
				"qiandan" => 0,
				"fabu" => 0,
				"huikuan" => 0,
				"qiankuan" => 0
			);
		}
		
		$dealers_db = Zend_Registry::get('dealers_db');
		
		$data = $dealers_db->select();
		$data->from("b_contract as c", array("contract_id", "sales_emp_id", "sales_emp_name", "receivable_value", "received_value"));
		$data->joinLeft("b_contract_sku as s", "s.contract_id=c.contract_id", array());
		$data->joinLeft("b_client as l", "l.client_id=c.client_id", array());
		$data->joinLeft("t_dealer_factory as df", "df.dealerId=l.is_dealer", array());
		$data->joinLeft("cars_db.t_factory as f", "f.id=df.factoryId", array());
		
		if($this->date_start)
		{
			$data->where("c.sign_date >= ?", $this->date_start);
		}
		
		if($this->date_end)
		{
			$data->where("c.sign_date <= ?", $this->date_end);
		}
		
		if($this->product_id)
		{
			$data->where("s.product_id = ?", $this->product_id);
		}
		
		if($this->province_id)
		{
			$data->where("c.province_id = ?", $this->province_id);
		}
		
		if($this->city_id)
		{
			$data->where("c.city_id = ?", $this->city_id);
		}
		
		if($this->factory_id)
		{
			$data->where("f.id = ?", $this->factory_id);
		}
		
		if($this->keyword)
		{
			$data->where("c.client_name like '%".$this->keyword."%'");
		}
		
		$data->where("c.sales_emp_id IN (?)", $my_staff_ids);
		
		$data->where("c.status != ?", 7);
		$data->where("c.contract_sid is not NULL or c.contract_sid != ''");
		$data->group("c.contract_id");
		
		$rows = $dealers_db->fetchAll($data);
		
//		$ext = new Sys_Extensions();
//		$ext->FormatArray($rows);die;
		
		$contract_id_arr = array();
		
		if(!empty($rows))
		{
			foreach($rows as $row)
			{
				if(!in_array($row['contract_id'], $contract_id_arr))
				{
					$contract_id_arr[] = $row['contract_id'];
					
					$result[$row['sales_emp_id']]['qiandan'] += $row['receivable_value'];
					$result[$row['sales_emp_id']]['huikuan'] += $row['received_value'];
					$result[$row['sales_emp_id']]['qiankuan'] += ($row['receivable_value'] - $row['received_value']);
				}
			}
		}
		
		//开始计算发布额
		$data = $dealers_db->select();
		$data->from("b_contract as c", array("contract_id", "sales_emp_id", "receivable_value", "start_date", "expired_date"));
		$data->joinLeft("b_contract_sku as s", "s.contract_id=c.contract_id", array());
		$data->joinLeft("b_client as l", "l.client_id=c.client_id", array());
		$data->joinLeft("t_dealer_factory as df", "df.dealerId=l.is_dealer", array());
		$data->joinLeft("cars_db.t_factory as f", "f.id=df.factoryId", array());
		
		if($this->date_start)
		{
			$data->where("c.expired_date >= ?", $this->date_start);
		}
		
		if($this->date_end)
		{
			$data->where("c.start_date <= ?", $this->date_end);
		}
		
		if($this->product_id)
		{
			$data->where("s.product_id = ?", $this->product_id);
		}
		
		if($this->province_id)
		{
			$data->where("c.province_id = ?", $this->province_id);
		}
		
		if($this->city_id)
		{
			$data->where("c.city_id = ?", $this->city_id);
		}
		
		if($this->factory_id)
		{
			$data->where("f.id = ?", $this->factory_id);
		}
		
		if($this->keyword)
		{
			$data->where("c.client_name like '%".$this->keyword."%'");
		}
		
		$data->where("c.sales_emp_id IN (?)", $my_staff_ids);
		
		$data->where("c.status != ?", 7);
		$data->where("c.contract_sid is not NULL or c.contract_sid != ''");
		$data->group("c.contract_id");
		
		$rows = $dealers_db->fetchAll($data);
		
		$fabu_result = $this->FaBu($rows, $this->date_start, $this->date_end);
		
		if(!empty($fabu_result))
		{
			foreach($fabu_result as $fabu_info)
			{
				$result[$fabu_info['emp_id']]['fabu'] += $fabu_info['fabu_value'];
			}
		}
		
//		$ext = new Sys_Extensions();
//		$ext->FormatArray($result);die;
		return $result;
	}
	
	private function FaBu($contract_rows, $date_start, $date_end) //发布额计算
	{
		$result = array();
//		$ext = new Sys_Extensions();
//		$ext->FormatArray($contract_rows);die;
		if(!empty($contract_rows) && $date_start && $date_end)
		{
			$contract_id_arr = array();
			
			foreach($contract_rows as $row)
			{
				if(!in_array($row['contract_id'], $contract_id_arr))
				{
					$contract_id_arr[] = $row['contract_id'];
					
					if($row['start_date'] < $date_start)
					{
						$actual_date_start = $date_start;
					}else{
						$actual_date_start = $row['start_date'];
					}

					if($row['expired_date'] > $date_end)
					{
						$actual_date_end = $date_end;
					}else{
						$actual_date_end = $row['expired_date'];
					}

					$actual_date_start_timestamp = mktime(0,0,0,substr($actual_date_start,5,2),substr($actual_date_start,8,2),substr($actual_date_start,0,4));
					$actual_date_end_timestamp = mktime(0,0,0,substr($actual_date_end,5,2),substr($actual_date_end,8,2),substr($actual_date_end,0,4));
					$actual_seconds = $actual_date_end_timestamp - $actual_date_start_timestamp;

					$contract_date_start_timestamp = mktime(0,0,0,substr($row['start_date'],5,2),substr($row['start_date'],8,2),substr($row['start_date'],0,4));
					$contract_date_end_timestamp = mktime(0,0,0,substr($row['expired_date'],5,2),substr($row['expired_date'],8,2),substr($row['expired_date'],0,4));
					$contract_seconds = $contract_date_end_timestamp - $contract_date_start_timestamp;

					if(0 < $actual_seconds && 0 < $contract_seconds)
					{
						$percent = $actual_seconds / $contract_seconds;

						$result[$row['contract_id']] = array(
							"emp_id" => $row['sales_emp_id'],
							"fabu_value" => $row['receivable_value'] * $percent
							);
					}
				}
			}
		}
		
		return $result;
	}
	
	function CityList()
	{
		$result = array(
			'ct' => 0,
			'total_pages' => 0,
			'current_page_info' => array()
		);
		
		$dealers_db = Zend_Registry::get("dealers_db");
		$hxb_db = Zend_Registry::get("hxb_db");
		
		$cities = array();
		$city_ids = array();
		
		$select = $dealers_db->select();
		$select->from("t_area", array("count(*) as ct"));
		
		if($this->province_id)
		{
			$select->where("provinceId = ?", $this->province_id);
		}
		
		if($this->city_id)
		{
			$select->where("cityId = ?", $this->city_id);
		}
		
		$row = $dealers_db->fetchRow($select);
		
		$result['ct'] = $row['ct'];
		$result['total_pages'] = ceil($row['ct'] / $this->page_size);
		
		$select = $dealers_db->select();
		$select->from("t_area", array("cityId", "provinceName", "cityName"));
		
		if($this->province_id)
		{
			$select->where("provinceId = ?", $this->province_id);
		}
		
		if($this->city_id)
		{
			$select->where("cityId = ?", $this->city_id);
		}
		
		$select->order(array("provinceLetter", "provinceName", "cityLetter"));
		
		if($this->page_id && !$this->skip_pagination)
		{
			$select->limit($this->page_size, $this->page_size * ($this->page_id - 1));
		}
		
		$data = $dealers_db->fetchAll($select);
		
		if(!empty($data))
		{
			foreach($data as $d)
			{
				$city_ids[] = $d['cityId'];
				$cities[$d['cityId']] = array("city_name" => $d['cityName']);
			}
		}
        
        $mod_params = new Db_Tables2_BDictParams();
        $sys_id = $mod_params->GetSysId();
		
		if(!empty($city_ids))
		{
			$select = $dealers_db->select();
			$select->from("t_dealer", array("cityId", "count(*) as ct"));
			$select->where("state = ?", 1);
			$select->where("cityId IN (?)", $city_ids);
			$select->group("cityId");
			$rows = $dealers_db->fetchAll($select);
			
			if(!empty($rows))
			{
				foreach($rows as $row)
				{
					$cities[$row['cityId']]['dealer_total'] = $row['ct'];
				}
			}
			
			$select = $hxb_db->select();
			$select->from("hxb_db".$sys_id.".t_hxb_oc_log as l", array());
			$select->joinLeft("dealers_db".$sys_id.".t_dealer as d", "d.id=l.dealerId", array("cityId", "count(*) as ct"));
			$select->where("l.date = ?", date("Y-m-d", mktime(1,1,1,date("m"),date("d")-1,date("Y"))));
			$select->where("d.cityId IN (?)", $city_ids);
			$select->where("d.state = ?", 1);
			$select->group("d.cityId");
			$rows = $dealers_db->fetchAll($select);
			
			if(!empty($rows))
			{
				foreach($rows as $row)
				{
					$cities[$row['cityId']]['hxb_total'] = $row['ct'];
				}
			}
			
			$select = $hxb_db->select();
			$select->from("hxb_db".$sys_id.".t_hxb_oc_log as l", array("dealerId", "sum(dayActiveCount) as ac"));
			$select->joinLeft("dealers_db".$sys_id.".t_dealer as d", "d.id=l.dealerId", array("cityId"));
			$select->where("l.date >= ?", date("Y-m-d", mktime(1,1,1,date("m"),date("d")-7,date("Y"))));
			$select->where("d.cityId IN (?)", $city_ids);
			$select->where("d.state = ?", 1);
			$select->group("l.dealerId");
			$select->having("ac > 6");
			$rows = $dealers_db->fetchAll($select);
			
//			$ext = new Sys_Extensions();
//			$ext->FormatArray($rows);
//			die;
			
			if(!empty($rows))
			{
				foreach($rows as $row)
				{
					if(!isset($cities[$row['cityId']]['hxb_active']))
					{
						$cities[$row['cityId']]['hxb_active'] = 0;
					}
					
					$cities[$row['cityId']]['hxb_active'] += 1;
				}
			}
			
//			$ext = new Sys_Extensions();
//			$ext->FormatArray($cities);
//			die;
		
			$result['current_page_info'] = $cities;
		}
		
		return $result;
	}
	
	function CallHistory()
	{
		$result = array(
			'total_pages' => 0,
			'current_page_info' => array()
		);
		
		//count total for pagination
		$maozhua_wb = Zend_Registry::get('maozhua_wb');
		
		$data = $maozhua_wb->select();
		$data->from("dcc_call_record as l", array("count(*) as ct"));
		$data->joinLeft("dcc_users as u", "u.call_id=l.cno", array());
		
		if($this->company_id)
		{
			$data->where("u.company_id = ?", $this->company_id);
		}
		
		if($this->date_start)
		{
			$data->where("l.start_time >= ?", $this->date_start." 00:00:00");
		}
		
		if($this->date_end)
		{
			$data->where("l.start_time <= ?", $this->date_end." 23:59:59");
		}
		
		if($this->call_in_num)
		{
			$data->where("l.customer_number = ?", $this->call_in_num);
		}
		
		if($this->call_phone)
		{
			$data->where("u.call_phone = ?", $this->call_phone);
		}
		
		if($this->call_in_out)
		{
			
			$data->where("l.record_type = ?", $this->call_in_out);
		}
		
		if(is_numeric($this->is_pick))
		{
			if($this->is_pick)
			{
				$data->where("l.bridge_duration != ?", "00:00:00");
			}else{
				$data->where("l.bridge_duration = ?", "00:00:00");
			}
		}
		
		if($this->user_name)
		{
			$data->where("u.user_name like '%".$this->user_name."%'");
		}

		$ct = $maozhua_wb->fetchRow($data);
		
		//dump all in condition
		$data = $maozhua_wb->select();
		$data->from("dcc_call_record as l", array("call_record_id", "customer_number", "start_time", "bridge_time", "total_duration", "bridge_duration", "actual_price", "record_type"));
		$data->joinLeft("dcc_users as u", "u.call_id=l.cno", array("user_name", "call_phone"));
		
		if($this->company_id)
		{
			$data->where("u.company_id = ?", $this->company_id);
		}
		
		if($this->date_start)
		{
			$data->where("l.start_time >= ?", $this->date_start." 00:00:00");
		}
		
		if($this->date_end)
		{
			$data->where("l.start_time <= ?", $this->date_end." 23:59:59");
		}
		
		if($this->call_in_num)
		{
			$data->where("l.customer_number = ?", $this->call_in_num);
		}
		
		if($this->call_phone)
		{
			$data->where("u.call_phone = ?", $this->call_phone);
		}
		
		if($this->call_in_out)
		{
			
			$data->where("l.record_type = ?", $this->call_in_out);
		}
		
		if(is_numeric($this->is_pick))
		{
			if($this->is_pick)
			{
				$data->where("l.bridge_duration != ?", "00:00:00");
			}else{
				$data->where("l.bridge_duration = ?", "00:00:00");
			}
		}
		
		if($this->user_name)
		{
			$data->where("u.user_name like '%".$this->user_name."%'");
		}

		if($this->page_id && !$this->skip_pagination)
		{
			$data->limit($this->page_size, $this->page_size * ($this->page_id - 1));
		}
		
		$data->order(array("l.call_record_id DESC"));
		
		$origine = $maozhua_wb->fetchAll($data);
		
		$current_page_info = array();
		
		if(!empty($origine))
		{
			$current_page_info = array(
				"customer_number" => array(),
				"start_time" => array(),
				"bridge_time" => array(),
				"total_duration" => array(),
				"bridge_duration" => array(),
				"actual_price" => array(),
				"record_type" => array(),
				"user_name" => array(),
				"call_phone" => array()
			);
			
			foreach($origine as $row)
			{
				$current_page_info['customer_number'][$row['call_record_id']] = $row['customer_number'];
				$current_page_info['start_time'][$row['call_record_id']] = $row['start_time'];
				$current_page_info['bridge_time'][$row['call_record_id']] = $row['bridge_time'];
				$current_page_info['total_duration'][$row['call_record_id']] = $row['total_duration'];
				$current_page_info['bridge_duration'][$row['call_record_id']] = $row['bridge_duration'];
				$current_page_info['actual_price'][$row['call_record_id']] = $row['actual_price'];
				$current_page_info['record_type'][$row['call_record_id']] = $row['record_type'];
				$current_page_info['user_name'][$row['call_record_id']] = $row['user_name'];
				$current_page_info['call_phone'][$row['call_record_id']] = $row['call_phone'];
			}
		}
		
		$result = array(
			'ct' => $ct['ct'],
			'total_pages' => ceil($ct['ct'] / $this->page_size),
			'current_page_info' => $current_page_info
		);
		
		return $result;
	}
}